package guest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import member.MemberVo;
import guest.GuestVo;

public class Guest {

	// -------------------------------------------------
	// JDBC 연결
	// -------------------------------------------------

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;
	
	GuestVo vo;

	public Guest() {

		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();

			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);

			// 3) PreparedStatement 작성
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public Vector<GuestVo> booklist(){
		Vector<GuestVo> v = new Vector<>();   
		String sql = "select * from guestbook where mid like ? or subject like ? or content like ? order by mdate" ;
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+ vo.getFind() +"%");
			ps.setString(2, "%"+ vo.getFind() +"%");
			ps.setString(3, "%"+ vo.getFind() +"%");
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				
				GuestVo gv = new GuestVo();
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getDate("mdate"));
				
				v.add(gv);
			}
			
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return v;
	}
	public boolean insert(){
		boolean flag=false;
		String sql = "insert into guestbook(serial,mid,subject,content) values(gb_serial.nextval, ?, ?, ?)";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getMid());
			ps.setString(2, vo.getSubject());
			ps.setString(3, vo.getContent());
			
			int cnt = ps.executeUpdate();
			if(cnt > 0) flag=true;
			
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
	public GuestVo view(){
		GuestVo gv = null;
		String sql = "select * from guestbook where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				gv = new GuestVo();
				
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getDate("mdate"));
				
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return gv;
	}
	
	public boolean delete(){
		boolean flag=false;
		String sql = "delete from guestbook where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt > 0) flag=true;
			
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return flag;
	}
	
	public boolean modify(){
		boolean flag=false;
		String sql = "update guestbook set subject=?, content=?, mid=? where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getSubject());
			ps.setString(2, vo.getContent());
			ps.setString(3, vo.getMid());
			ps.setInt(4, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0) flag=true;
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}

	public GuestVo getVo() {
		return vo;
	}

	public void setVo(GuestVo vo) {
		this.vo = vo;
	}
	
	
}
